﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WHMS.EntityClasses;
using WHMS.HelperClasses;
using WHMS.DatabaseSpecific;
using SD.LLBLGen.Pro.ORMSupportClasses;
using WHMS.FactoryClasses;
using System.Data;

namespace WMS.HelperClasses
{
	public static class DALHelper
	{
		public static bool AuthenticateUser(string userName, string pswd)
		{
			userName = userName.Trim();
			pswd = pswd.Trim();

			if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(pswd))
				return false;

			using (var adapter = new DataAccessAdapter())
			{
				var fields = new EntityFields2(1);
				fields.Add(UserFields.Id);

				var id = adapter.GetScalar(UserFields.Id, null, AggregateFunction.None, new PredicateExpression(UserFields.UserName == userName & UserFields.Password == pswd));
				if (id == null)
					return false;

				UserInfo.UserId = (int)id;

				var roles = new EntityCollection<RoleEntity>();
				var filter = new RelationPredicateBucket(new FieldCompareSetPredicate(RoleFields.Id, null, UserRoleFields.RoleId, null, SetOperator.In, new PredicateExpression(UserRoleFields.UserId == (int)id)));
				FetchCollection(roles, filter);

				UserInfo.Roles = roles;

				return true;
			}			 
		}
		public static bool SaveEntity(IEntity2 entity)
		{
			return SaveEntity(entity, false);
		}
		public static bool SaveEntity(IEntity2 entity, bool recurse)
		{
			var result = false;
			using (var adapter = new DataAccessAdapter())
			{
				result = adapter.SaveEntity(entity, false, recurse);
			}

			return result;
		}
		public static bool DeleteEntity(IEntity2 entity)
		{
			var result = false;
			using (var adapter = new DataAccessAdapter())
			{
				result = adapter.DeleteEntity(entity);
			}

			return result;
		}

		public static void FetchCollection(IEntityCollection2 collection, RelationPredicateBucket filter, PrefetchPath2 preftchPath, SortExpression sorter)
		{
			using (var adapter = new DataAccessAdapter())
			{
				adapter.FetchEntityCollection(collection, filter, 0, sorter, preftchPath);
			}
		}
		public static void FetchCollection(IEntityCollection2 collection, RelationPredicateBucket filter, PrefetchPath2 preftchPath)
		{
			using (var adapter = new DataAccessAdapter())
			{
				adapter.FetchEntityCollection(collection, filter, preftchPath);
			}
		}

        public static void FetchCollection(IEntityCollection2 collection, RelationPredicateBucket filter, PrefetchPath2 preftchPath, int _currentPageIndex, int _pageSize)
        {
            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(collection, filter, 0, null, preftchPath, null, _currentPageIndex, _pageSize);

            }
        }
		public static void FetchCollection(IEntityCollection2 collection, RelationPredicateBucket filter)
		{
			FetchCollection(collection, filter, null);
		}
		public static void FetchCollection(IEntityCollection2 collection)
		{
			FetchCollection(collection, null);
		}
		public static int SaveCollection(IEntityCollection2 collection)
		{			
			using (var adapter = new DataAccessAdapter())
			{
				return  adapter.SaveEntityCollection(collection);
			}
		}
		public static int DeleteCollection(IEntityCollection2 collection)
		{
			using (var adapter = new DataAccessAdapter())
			{
				return adapter.DeleteEntityCollection(collection);
			}			
		}

		public static void SaveMNAssociationEntities(Type type,IEntityCollection2 collection, EntityField2 filterField, int filterId)
		{
			using (var adapter = new DataAccessAdapter(true))
			{
				adapter.StartTransaction(IsolationLevel.ReadCommitted, type.Name);
				try
				{
					adapter.DeleteEntitiesDirectly(type, new RelationPredicateBucket(filterField == filterId));
					adapter.SaveEntityCollection(collection);
					adapter.Commit();
				}
				catch { adapter.Rollback(); }
			}

		}

		public static DataTable GetMNUnrelatedItems(EntityField2 valueMember, EntityField2 displayMember, EntityField2 joinedField, EntityField2 filterField, int filterId)
		{
			return GetMNUnrelatedItems(valueMember, displayMember,  joinedField, filterField, filterId, true);
		}
		public static DataTable GetMNRelatedItems(EntityField2 valueMember, EntityField2 displayMember, EntityField2 joinedField, EntityField2 filterField, int filterId)
		{
			return GetMNUnrelatedItems(valueMember, displayMember, joinedField, filterField, filterId, false);
		}
		private static DataTable GetMNUnrelatedItems(EntityField2 valueMember, EntityField2 displayMember, EntityField2 joinedField, EntityField2 filterField, int filterId, bool negate)
		{
			var filter = new RelationPredicateBucket(new FieldCompareSetPredicate(valueMember, null, joinedField, null, SetOperator.In, new PredicateExpression(filterField == filterId), negate));

			return GetDynamicList(valueMember, displayMember, filter);
		}
		public static DataTable GetDynamicList(EntityField2 valueMember, EntityField2 displayMember, IRelationPredicateBucket filter)
		{
			var sorter = new SortExpression(displayMember | SortOperator.Ascending);
			return GetDynamicList(valueMember, displayMember, filter, sorter);
		}
		public static DataTable GetDynamicList(EntityField2 valueMember, EntityField2 displayMember, IRelationPredicateBucket filter, ISortExpression sorter)
		{
			var fields = new ResultsetFields(2);
			fields.DefineField(valueMember, 0);
			fields.DefineField(displayMember, 1);

			var dt = new DataTable();

			using (var adapter = new DataAccessAdapter())
			{
				adapter.FetchTypedList(fields, dt, filter, 0, sorter, false);
				return dt;
			}
		}
		
		public static DataTable GetDynamicList(ResultsetFields fields, IRelationPredicateBucket filter, ISortExpression sorter)
		{
			var dt = new DataTable();

			using (var adapter = new DataAccessAdapter())
			{
				adapter.FetchTypedList(fields, dt, filter, 0, sorter, false);
				return dt;
			}
		}

		public static object GetValue(EntityField2 requiredField, EntityField2 filterField, object filterValue)
		{
			//using (var adapter = new DataAccessAdapter())
			//{
			//    return adapter.GetScalar( requiredField, null, AggregateFunction.None, new PredicateExpression(filterField == filterValue)); 
			//}

			return GetValue(requiredField, AggregateFunction.None, new PredicateExpression(filterField == filterValue));
		}

		public static bool SaveUoW(UnitOfWork2 uow)
		{
			using (var adapter = new DataAccessAdapter())
			{
				adapter.StartTransaction(IsolationLevel.ReadCommitted, "AddBalance");
				try
				{
					uow.Commit(adapter);
					adapter.Commit();
					return true;
				}
				catch 
				{
					adapter.Rollback();
					return false;
				}			
			}
		}

		public static object GetValue(EntityField2 requiredField, AggregateFunction aggregate, PredicateExpression filter)
		{
			return GetValue(requiredField, aggregate, filter, null);
		}

		public static object GetValue(EntityField2 requiredField, AggregateFunction aggregate, PredicateExpression filter, RelationCollection relations)
		{
			using (var adapter = new DataAccessAdapter())
			{
				return adapter.GetScalar(requiredField, null, aggregate, filter, null, relations);
			}		
		}

		public static void GetTypedList(ITypedListLgp2 list, IPredicateExpression filter, ISortExpression sorter)
		{
			using (var adapter = new DataAccessAdapter())
			{
				adapter.FetchTypedList(list, filter, 0, sorter, false);
			}
		}

		public static object UpdateEntities(IEntity2 entity, IRelationPredicateBucket filter)
		{
			using (var adapter = new DataAccessAdapter())
			{
				return adapter.UpdateEntitiesDirectly(entity, filter);
			}
		}

		public static bool CheckExistingValue(EntityField2 field, string value, EntityField2 pkField, int pkValue)
		{
			using (var adapter = new DataAccessAdapter())
			{
				var fields = new EntityFields2(1);
				fields.Add(field);
				return (adapter.GetDbCount(fields, new RelationPredicateBucket(field == value & pkField != pkValue))) > 0;
			}					
		}

		public static int GetCount(IEntityCollection2 collection, RelationPredicateBucket filter)
		{
			using (var adapter = new DataAccessAdapter())
			{
				return adapter.GetDbCount(collection, filter);
			}							
		}

		public static void GetTypedView(ITypedView2 view, IRelationPredicateBucket filter, ISortExpression sorter)
		{
			using (var adapter = new DataAccessAdapter())
			{
				adapter.FetchTypedView(view, filter, 0, sorter, false);
			}		
		}
	}
}
